﻿<%@ WebHandler Language="C#" Class="ZSEmployeeAllTypeWorktimeReport" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Collections;
using System.IO;
using ReportBusiness;

/**
 * Description: 装配试车厂--各类型工时数据统计报表
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.3.29
 */

public class ZSEmployeeAllTypeWorktimeReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询sql
    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT tt.employeename,tt.fullname,tt.teamname,
       NVL(SUM(CASE WHEN tt.worktimetype = '油封工时' THEN tt.worktime ELSE 0 END),0) oilworktime,
       NVL(SUM(CASE WHEN tt.worktimetype = '施工工时' THEN tt.worktime ELSE 0 END),0) sgdworktime,
       NVL(SUM(CASE WHEN tt.worktimetype = '批产工时' THEN tt.worktime ELSE 0 END),0) pichanworktime,
       SUM(tt.worktime) totalworktime
FROM (
    SELECT t.employeename,t.fullname,t.teamname,ROUND(SUM(t.worktime)/60,2) worktime,'批产工时' worktimetype
    FROM (
      SELECT NVL(e2.employeename,e.employeename) employeename,
             NVL(e2.fullname,e.fullname) fullname,
             wt.machineno,wt.checktype,wt.bigworkflowstepname,t.teamname,
             CASE WHEN wt.stepinfoid IS NOT NULL AND wt.checktype ='1'
                  THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(si.durationperunit / NVL(wt.splitcount, 1), 2), 0) 
                  WHEN wt.stepinfoid IS NOT NULL AND wt.checktype ='2'
                  THEN COALESCE(wts.splitduration,wt.totalduration, ROUND(si.durationperunit2 / NVL(wt.splitcount, 1), 2), 0)  
                  ELSE COALESCE(wt.totalduration, wt.durationperunit, wts.splitduration, ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0) 
             END worktime
      FROM containersteptimesheet wt
      LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
      LEFT JOIN team t ON t.teamid = wt.teamid
      LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
      LEFT JOIN stepinfo si ON si.stepinfoid = wt.stepinfoid
      LEFT JOIN factory f ON f.factoryid = wt.factoryid
      LEFT JOIN employee e ON e.employeeid = wt.workerid
      LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
      WHERE f.factoryname = '{0}' AND wt.checkdate >= to_date('{1}','yyyy/mm/dd') AND wt.checkdate <= to_date('{2}','yyyy/mm/dd')";

        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.employeename,e.employeename) = '{0}'", jObject["employeename"].ToString());
        }
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.fullname,e.fullname) = '{0}'", jObject["fullname"].ToString());
        }
        if (jObject["teamname"] != null && jObject["teamname"].ToString() != "")
        {
            sql += string.Format(" AND t.teamname = '{0}'", jObject["teamname"].ToString());
        }

        sql += @"
    ) t
    GROUP BY t.employeename,t.fullname,t.teamname

    UNION ALL

    SELECT t.employeename,t.fullname,t.teamname,ROUND(SUM(t.worktime)/60,2) worktime,'施工工时' worktimetype
    FROM (
      SELECT NVL(e2.employeeid,e.employeeid) employeeid,
             NVL(e2.employeename,e.employeename) employeename,
             NVL(e2.fullname,e.fullname) fullname,
             ws.workflowstepname,
             ws.description wsdescription,
             s.description stepinfodesc,
             wt.createdate reportdate,
             wt.checkdate,
             wt.machineno,
             t.teamname,
             CASE WHEN(wt.stepinfoid IS NOT NULL AND wt.checktype ='1') THEN NVL(wt.durationperunit,s.durationperunit)
                  WHEN(wt.stepinfoid IS NOT NULL AND wt.checktype ='2') THEN NVL(wt.durationperunit,s.durationperunit2)
                  ELSE NVL(wt.durationperunit,sd.durationperunit)
             END durationperunit,
             CASE WHEN wt.stepinfoid IS NOT NULL AND wt.checktype ='1' THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(s.durationperunit / NVL(wt.splitcount, 1), 2), 0) 
                  WHEN wt.stepinfoid IS NOT NULL AND wt.checktype ='2' THEN COALESCE(wts.splitduration,wt.totalduration, ROUND(s.durationperunit2 / NVL(wt.splitcount, 1), 2), 0)  
                  ELSE COALESCE(wt.totalduration,wt.durationperunit,wts.splitduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0) 
             END worktime,
             DECODE(wt.checktype,'2','检装','初装') checktype
      FROM containersteptimesheet wt
      LEFT JOIN enginepartplan epl ON NVL(epl.enginecontainerid,epl.containerid) = wt.containerid 
      LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
      LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid --工序单位工时
      LEFT JOIN stepinfo s ON s.stepinfoid = wt.stepinfoid --工步单位工时
      LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid
      LEFT JOIN employee e ON e.employeeid = wt.workerid
      LEFT JOIN employee e2 ON e2.employeeid = wts.workerid --工时分配工人
      LEFT JOIN factory f ON f.factoryid = wt.factoryid
      LEFT JOIN team t ON t.teamid = wt.teamid
      WHERE wt.workerid IS NOT NULL AND epl.workflowstepid IS NULL
            AND f.factoryname = '{0}' AND wt.checkdate >= to_date('{1}','yyyy/mm/dd') AND wt.checkdate <= to_date('{2}','yyyy/mm/dd')";

        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.employeename,e.employeename) = '{0}'", jObject["employeename"].ToString());
        }
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.fullname,e.fullname) = '{0}'", jObject["fullname"].ToString());
        }
        if (jObject["teamname"] != null && jObject["teamname"].ToString() != "")
        {
            sql += string.Format(" AND t.teamname = '{0}'", jObject["teamname"].ToString());
        }

        sql += @"
    ) t
    GROUP BY t.employeename,t.fullname,t.teamname

    UNION ALL

    SELECT t.employeename,t.fullname,t.teamname,ROUND(SUM(t.worktime)/60,2) worktime,'油封工时' worktimetype
    FROM (
        SELECT e.employeename,
               e.fullname,
               es.containerno,
               t.teamname,
               es.createdate reporttime,
               es.createdate checkdate,
               os.oilsealdurationperunit,
               ROUND(os.oilsealdurationperunit/NVL(ec.workercount,1),2) worktime
        FROM enginepartoilseal es
        LEFT JOIN oilsealcycleinfo os ON es.oilsealcycleinfoid=os.oilsealcycleinfoid
        INNER JOIN enginepartoilsealworker ew ON es.enginepartoilsealid=ew.enginepartoilsealid
        LEFT JOIN (SELECT ew.enginepartoilsealid,COUNT(1) workercount  
                   FROM enginepartoilsealworker ew 
                   GROUP BY ew.enginepartoilsealid
                  ) ec ON ec.enginepartoilsealid=es.enginepartoilsealid
        LEFT JOIN employee e ON e.employeeid = ew.employeeid
        LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid
        LEFT JOIN factory f ON f.factoryid = sv.factoryid
        LEFT JOIN teamemployee te ON te.employeeid=e.employeeid
        LEFT JOIN team t ON t.teamid = te.teamid
        WHERE f.factoryname = '{0}' AND es.createdate >= to_date('{1}','yyyy/mm/dd') AND es.createdate <= to_date('{2}','yyyy/mm/dd')";

        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND e.employeename = '{0}'", jObject["employeename"].ToString());
        }
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", jObject["fullname"].ToString());
        }
        if (jObject["teamname"] != null && jObject["teamname"].ToString() != "")
        {
            sql += string.Format(" AND t.teamname = '{0}'", jObject["teamname"].ToString());
        }

        sql += @"
    ) t
    GROUP BY t.employeename,t.fullname,t.teamname
) tt
GROUP BY tt.employeename,tt.fullname,tt.teamname";

        string factoryname = jObject["factoryname"].ToString();
        JArray arr = JArray.Parse(jObject["checkdate"].ToString());
        if (true)
        {

        }
        sql = string.Format(sql, factoryname, arr[0].ToString(), arr[1].ToString());
        return sql;
    }

    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["fullname"].Caption = "姓名";
        dt.Columns["employeename"].Caption = "员工号";
        dt.Columns["teamname"].Caption = "小组名称";
        dt.Columns["oilworktime"].Caption = "油封工时";
        dt.Columns["sgdworktime"].Caption = "临时工时";
        dt.Columns["pichanworktime"].Caption = "批次工时";
        dt.Columns["totalworktime"].Caption = "工时汇总";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "员工各类型工时统计报表");
    }
}